package com.monitor.dao;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.monitor.entity.ErpMaterielFlowSummary;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 物料流转统计表 Mapper 接口
 * </p>
 *
 * @author LX
 * @since 2018-09-08
 */
public interface ErpMaterielFlowSummaryMapper extends BaseMapper<ErpMaterielFlowSummary> {


    @Select("SELECT IFNULL(SUM(materiel_outflow_number),0) FROM erp_materiel_flow_summary WHERE materiel_id=#{matId} AND out_warehouse_id= #{wareId} AND materiel_flow_type=1 and materiel_flow_date < #{startTime} and  materiel_flow_date> #{endTime}")
    Integer getMatOutNum(@Param("matId")Integer matId,@Param("wareId")Integer wareId,@Param("startTime")String startTime,@Param("endTime")String endTime);//查询物料时间段内出库总数



    @Select("SELECT IFNULL(SUM(materiel_storage_number),0) FROM erp_materiel_flow_summary WHERE materiel_id=#{matId} AND warehouse_id=#{wareId} AND materiel_flow_type=0 and materiel_flow_date < #{startTime} and materiel_flow_date > #{endTime}")
    Integer getMatInNum(@Param("matId") Integer matId,@Param("wareId") Integer wareId,@Param("startTime") String StartTime,@Param("endTime") String endTime);//查询物料时间段内入库总数

    @Select("SELECT * FROM erp_materiel_flow_summary WHERE materiel_id=#{matId}  and materiel_flow_date <#{StartTime} AND warehouse_id=#{wareId} or out_warehouse_id=#{wareId} order by id desc")
    List<ErpMaterielFlowSummary> getSummary(@Param("matId") Integer matId,@Param("wareId") Integer wareId,@Param("StartTime") String StartTime);


    @Select("SELECT * FROM erp_materiel_flow_summary WHERE materiel_id=#{matId}  and materiel_flow_date < #{StartTime}  and materiel_flow_date < #{endDate} AND warehouse_id=#{wareId} or out_warehouse_id=#{wareId} order by id desc")
    List<ErpMaterielFlowSummary> getSummaryList(@Param("matId") Integer matId,@Param("wareId") Integer wareId,@Param("StartTime") String StartTime,@Param("endDate") String endDate);


    @Select("SELECT * FROM erp_materiel_flow_summary WHERE materiel_id=#{matId}  and materiel_flow_date < #{StartTime}  and materiel_flow_date < #{endDate} AND  out_warehouse_id=#{wareId}  and materiel_flow_type=1  and flow_subject!='车辆物料'   order by id desc")
    List<ErpMaterielFlowSummary> getOutMaterielToDate(@Param("matId") Integer matId,@Param("wareId") Integer wareId,@Param("StartTime") String StartTime,@Param("endDate") String endDate);

    @Select("SELECT * FROM erp_materiel_flow_summary WHERE materiel_id=#{matId}  and materiel_flow_date > #{StartTime} and materiel_flow_date <  #{endDate} AND warehouse_id=#{wareId} and materiel_flow_type=0  and flow_subject!='车辆物料'   order by id desc")
    List<ErpMaterielFlowSummary> getGoMaterielToDate(@Param("matId") Integer matId,@Param("wareId") Integer wareId,@Param("StartTime") String StartTime,@Param("endDate") String endDate);

    @Select("SELECT * FROM erp_materiel_flow_summary WHERE work_sheet_id LIKE ${sheelId} ORDER BY id desc LIMIT 0,1")
    public ErpMaterielFlowSummary selectBySheelId(@Param("sheelId")String sheelId);
    
    @Select("SELECT * FROM erp_materiel_flow_summary m WHERE m.warehouse_id in (${wareId})  and m.materiel_flow_date <= #{flowDate}  and (m.materiel_retained != 1 or m.materiel_retained is null)")
    public List<ErpMaterielFlowSummary> selectByWareId(@Param("wareId")String wareId,@Param("flowDate")String flowDate);
    
}
